# coding:utf-8
"""
AUTHOR:george.kong
DESC:TODO.
DATE:
"""
import logging
import random
import sys
import time
import traceback

import openpyxl
import pymysql
import yagmail

# import redis

host="127.0.0.1"
port = 3306
dbuser='root'
dbpwd='12345678'

def get_redis():
    """获取redis连接"""
    r = None
    try:
        pool = redis.ConnectionPool(host='localhost', port=6379, decode_responses=True)
        r = redis.Redis(connection_pool=pool)
    except Exception as E:
        logging.error("获取REDIS[{}]链接失败！\n".format(host+str(port))+str(E))
        logging.error(traceback.format_exc())
    # r.get(1)
    # r.delete(1)  # 删除key
    # r.hmset(int(id), {"name": name, "remark": remark})  # 将数据库提取的数据设置到redis中
    # name, remark = (r.hmget(int(id), "name", "remark"))
    # r.set("total_ggtt", total)  # 写redis：key为字符串，value为数字
    # total = int(r.get("total_ggtt"))  # 获取key为"total_ggtt"的值
    # r.incr("total_ggtt")  # 为key为”total_ggtt“的值增加1
    return r

def read_excel(efile='data.xlsx', sheet='Sheet1'):
    """
    读取指定 sheet页的 电子表格内容
    :param efile:
    :param sheet:
    :return:
    """
    result = []
    # 打开Excel文件
    workbook = openpyxl.load_workbook(efile)
    # 选择第一个工作表
    sheet = workbook.active
    # 遍历行和列，读取数据
    for row in sheet.iter_rows():
        for cell in row:
            print(cell.value)
    # TODO 读取指定sheet的所有行、列
    return result


def write_excel(efile):
    rcode = -1
    try:
        # 创建一个新的Excel文件对象
        workbook = openpyxl.Workbook()
        # 选择第一个工作表
        sheet = workbook.active
        # 写入数据
        data = [
            ['学号', '姓名', '成绩'],
            ['001', '张三', 90],
            ['002', '李四', 85],
            ['003', '王五', 95],
        ]
        for row in data:
            sheet.append(row)
        # 保存文件
        workbook.save(efile)
        rcode = 0
    except Exception as E:
        print(E)
        print(traceback.format_exc())
    return rcode


def set_simp_log(logname, loglevel=logging.DEBUG):
    logging.basicConfig(filename=logname, level=loglevel, format='%(asctime)s %(name)s %(filename)s %(lineno)d %(msg)s')


def set_log(logname, loglevel=logging.DEBUG):
    sh = logging.StreamHandler()
    fh = logging.FileHandler(logname)
    logging.basicConfig(level=loglevel, handlers=[sh, fh, ],
                        format='%(asctime)s %(name)s %(filename)s %(lineno)d %(msg)s')


def get_logger(name, filename, str_formatter, str_formatter2, level):
    mlogger = logging.getLogger(name)
    f_formater = logging.Formatter(str_formatter)
    # 文件处理器
    fh = logging.FileHandler(filename)
    fh.setFormatter(f_formater)

    # f_formater2 = logging.Formatter(str_formatter2)
    # 流处理器
    sh = logging.StreamHandler()
    # sh.setFormatter(f_formater2)
    mlogger.addHandler(sh)
    mlogger.addHandler(fh)
    mlogger.setLevel(level)
    return mlogger


class AMail:
    @staticmethod
    def send_email(filename, title="测试报告标题", content="这里是测试报告正文。"):
        mail = yagmail.SMTP(user='a113231805773@126.com', password='AKXFPIWZIDVXKHBQ', host='smtp.126.com')
        mail.send(to='9954693@qq.com', subject=title, contents=content, attachments=filename)

    @staticmethod
    def get_email(title="测试报告标题"):
        print(title)


dbhost = '127.0.0.1'
dbport = 3306
dbuser = 'root'
dbpwd = '12345678'

#导包
import pymysql

# 创建工具类
class DButil():
    # 初始化
    __conn = None
    __cursor = None

    # 创建连接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host="localhost",
                                         port=3306,
                                         user="root",
                                         password="123456",
                                         database="school"
                                         )
        return cls.__conn

    # 获取游标
    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    # 执行sql
    @classmethod
    def exe_sql(cls, sql):
        try:
            cursor = cls.__get_cursor()
            cursor.execute(sql)
            if sql.split()[0].lower() == "select":
                return cursor.fetchall()
            else:
                cls.__conn.commit()
                return cursor.rowcount
        except Exception as e:
            print(e)
            cls.__conn.rollback()
        finally:
            cls.__close_cursor()
            cls.__close_conn()

    # 关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    # 关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

def query_by_mysql(sql="select 1,2;", db='tests', dbuser=dbuser, dbpwd=dbpwd, dbhost=dbhost, dbport=dbport):
    result = []
    conn = pymysql.Connection(user=dbuser, password=dbpwd, host=dbhost, port=dbport)
    cur = conn.cursor()
    try:
        cur.execute("use {};".format(db))
        cur.execute(sql)
        for r in cur.fetchall():
            result.append(r)
        print("SQL语句[{}] 查询成功！".format(sql))
    except Exception as E:
        print("SQL语句[{}] 执行失败！".format(sql))
        logging.error("SQL语句[{}] 执行失败！".format(sql))
        logging.error(str(traceback.format_exc()))
    finally:
        # 关闭游标
        if cur:
            cur.close()
        # 关闭连接
        if conn:
            conn.close()
    return result


def exec_by_mysql(sqls=["update student set age=age-5 where id=1000;","update student set age=age-5 where id=1001;"], db='tests', dbuser=dbuser, dbpwd=dbpwd,
                  dbhost=dbhost, dbport=dbport):
    rcode = 0
    conn = pymysql.Connection(user=dbuser, password=dbpwd, host=dbhost, port=dbport)
    cur = conn.cursor()
    try:
        cur.execute("use {};".format(db))
        if isinstance(sqls,list):
            for sql in sqls:
                cur.execute(sql)
        else:
            cur.execute(sqls)
        conn.commit()  # 提交事务
        print("提交事务")
    except Exception as E:
        conn.rollback()  # 回滚事务
        rcode = -1
        logging.error("SQL语句[{}] 执行失败！".format(sql))
        logging.error(str(traceback.format_exc()))
    finally:
        # 关闭游标
        if cur:
            cur.close()
        # 关闭连接
        if conn:
            conn.close()
    return rcode

def create_data(times=500000):  # 造 数据
    loops = times/1000
    while loops > 0:
        create_data4goods()
        print(loops,"执行成功")
        loops -= 1

def create_data4goods(times=1000):  # 造 商品记录
    gnames = ["板擦","粉笔","扫帚","垃圾桶","拖把"]
    gprice= [5, 1, 15, 10, 8]
    sqls = []
    while times > 0:
        rd = random.Random()
        r = rd.randint(0,4)
        n = rd.randint(1, 50)
        gname = gnames[r]
        price = gprice[r]
        money = price * n
        randsecs = rd.randint(int(time.time()-7*3600*24),int(time.time()))
        optime = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(randsecs))
        insertsql ="insert into records (gname,num,money,optime) values ('{}',{},{},'{}')".format(gname,price,money,optime)
        # print(insertsql)
        sqls.append(insertsql)
        times -= 1
    exec_by_mysql(sqls, "redisdemo")

def init_redis_data():
    r = get_redis()  # 获取redis 连接
    # 以下设置 商品信息：商品名，商品库存，商品交易额
    r.hset("板擦", mapping={"amount": 1000, "allmoney": 27201965})
    r.hset("粉笔", mapping={"amount": 1000, "allmoney": 27201965})
    r.hset("扫帚", mapping={"amount": 1000, "allmoney": 81443190})
    r.hset("垃圾桶", mapping={"amount": 1000, "allmoney": 54415430})
    r.hset("拖把", mapping={"amount": 1000, "allmoney": 43537544})
    # 以下设置 总交易额： 交易额
    r.set("totalmoney", 300010000)

    # 以下设置 统计信息： 日期，交易量，交易额
    r.hset("2023-10-09", mapping={"amount": 404076, "allmoney": 10275541})
    r.hset("2023-10-10", mapping={"amount": 404076, "allmoney": 10275541})
    r.hset("2023-10-11", mapping={"amount": 404076, "allmoney": 10275541})
    r.hset("2023-10-12", mapping={"amount": 404076, "allmoney": 10275541})
    r.hset("2023-10-13", mapping={"amount": 404076, "allmoney": 10275541})
    r.hset("2023-10-14", mapping={"amount": 404076, "allmoney": 10275541})
    r.hset("2023-10-15", mapping={"amount": 404076, "allmoney": 10275541})

def ghome_date_sta():
    stasql = 'select left(optime,10) dd,sum(num),sum(money) from records group by left(optime,10) order by dd desc;'
    return query_by_mysql(stasql, 'redisdemo')

def ghome_amount():
    goodses_a = 'select gname,amount from goods order by amount desc limit 10;'
    return  query_by_mysql(goodses_a, 'redisdemo')

def ghome_money():
    goodses_m = 'select a.gname,sum(b.money) from (select gname,amount from goods order by amount desc limit 10) a,records b where a.gname = b.gname group by a.gname'
    return  query_by_mysql(goodses_m, 'redisdemo')

def ghome_totalmoney():
    totalsql = 'select sum(money) totalmoney from records';
    return  query_by_mysql(totalsql, 'redisdemo')
def getdata_homepage():  # 旧的首页 视图方法
    d1 = ghome_date_sta()
    d2 = ghome_amount()
    d3 = ghome_money()
    d4 = ghome_totalmoney()
    return d1,d2,d3,d4

def homepage_new():   # 新的首页 视图方法，优化了使用Redis
    r = get_redis()
    # 获取总交易
    dtotal = r.get("totalmoney")
    if not dtotal:
        dtotal = ghome_totalmoney()
    # 获取前十商品
    da = ghome_amount()
    # 获取前十商品 交易额
    dm = []
    for g in ["板擦","粉笔","扫帚","垃圾桶","拖把"]:
        a = r.hget(g,"amount")
        m = r.hget(g, "allmoney")
        if a == None or m == None:
            break
        dm.append({"a":a,"m":m})
    if len(dm)<5:
        dm = ghome_money()
    # 获取过去5天 统计数据
    ddata = []
    for g in ["2023-10-09","2023-10-10","2023-10-11","2023-10-12","2023-10-13","2023-10-14","2023-10-15"]:
        a = r.hget(g,"amount")
        m = r.hget(g, "allmoney")
        if a == None or m == None:
            break
        ddata.append({"a": a, "m": m})
    if len(ddata)<7:
        ddata = ghome_totalmoney()
    return da,dm,ddata,dtotal

if __name__ == '__main__':
    create_data4goods(50)
    # sys.exit()

    s = time.time()
    print(getdata_homepage())
    e = time.time()
    print(e-s)

    s = time.time()
    print(homepage_new())
    e = time.time()
    print(e-s)

    # sys.exit()
    #
    # r = get_redis()
    # #
    # r.hset("板擦", mapping={"amount": 1000, "allmoney": 27201965})
    # r.hset("粉笔", mapping={"amount": 1000, "allmoney": 27201965})
    # r.hset("扫帚", mapping={"amount": 1000, "allmoney": 81443190})
    # r.hset("垃圾桶", mapping={"amount": 1000, "allmoney": 54415430})
    # r.hset("拖把", mapping={"amount": 1000, "allmoney": 43537544})
    #
    # r.set("totalmoney", 300010000)
    # r.delete("totalmoney")
    #
    # r.hset("2023-10-09", mapping={"amount": 404076, "allmoney": 10275541})
    # r.delete("2023-10-09")
    # r.delete("2023-10-10")
    # r.hset("2023-10-10", mapping={"amount": 404076, "allmoney": 10275541})
    # r.hset("2023-10-11", mapping={"amount": 404076, "allmoney": 10275541})
    # r.hset("2023-10-12", mapping={"amount": 404076, "allmoney": 10275541})
    # r.hset("2023-10-13", mapping={"amount": 404076, "allmoney": 10275541})
    # r.hset("2023-10-14", mapping={"amount": 404076, "allmoney": 10275541})
    # r.hset("2023-10-15", mapping={"amount": 404076, "allmoney": 10275541})


    # r.get(1)
    # r.delete(1)  # 删除key
    # name, remark = (r.hmget(int(id), "name", "remark"))
    # r.set("total_ggtt", total)  # 写redis：key为字符串，value为数字
    # total = int(r.get("total_ggtt"))  # 获取key为"total_ggtt"的值
    # r.incr("total_ggtt")  # 为key为”total_ggtt“的值增加1

    # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))

    # create_data(500000)
    # print("50W插入成功")
    # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())))
    # res = exec_by_mysql()
    # sql = "select count(1) from pms_menu where name='{}'".format("M2101091503")
    # res = int(query_by_mysql(sql,"roncoo_mini_pay_demo",dbuser="work",dbpwd="txwk1234WK",dbhost="152.136.40.30")[0][0])
    # print(res)


    # AMail.send_email(filename='2_1.txt', title='20*2',
    #                  content='亲爱得你，巴拉巴拉巴777777777777777777777777777777777777777777777777777'
    #                          '77777777777777777777777777777777拉巴拉,4_1.txt')
    # send_email(filename='f1 .txt', title='这里是标题党', content='亲爱得你，巴拉巴拉巴拉巴拉,4_1.txt')
    # send_email(filename='a2101.log', title='这里是标题党', content='亲爱得你，巴拉巴巴爸爸阿爸,4_1.txt')
    # xlsfile = 'stu.xlsx'
    # write_excel('stu.xlsx')
    # print(read_excel(xlsfile))
